SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  FUNCTION 

dbo.GetSSBCusip (@LXID VARCHAR(50))
RETURNS TABLE --VARCHAR(50)

AS
RETURN

--DECLARE @SSBCusip VARCHAR(50);

----DECLARE @AssetID VARCHAR(50) = (
----	SELECT TOP 1 a.AssetId 
----	FROM FSFundModel.dbo.Asset a 
----	INNER JOIN FSFundModel.dbo.Instrument i 
----	ON a.InstrumentId = i.InstrumentId
----	WHERE i.ExtId = @LXID
----	--ORDER BY a.ExportId DESC 
----	)
--SET  @SSBCusip =(

SELECT 
DISTINCT
ssr.CUSIP, i.ExtId, i.InstrumentId  FROM  FSFundModel.dbo.StateStreetRecon ssr
INNER JOIN  FSFundModel.dbo.AssetIdentifier ai
ON ai.AssetId = ssr.StateStreetIdentifier
INNER JOIN FSFundModel.dbo.Asset a
ON 
 ai.InstrumentId = a.InstrumentId
INNER JOIN  FSFundModel.dbo.Instrument i ON
 ai.InstrumentId = i.InstrumentId
WHERE 
-- ai.AssetId = @AssetID and 
 i.ExtId = @LXID
 

--ORDER BY ssr.ImportId DESC
--)

--RETURN (@SSBCusip);
--END;

GO
